<?php


namespace app\common;
use app\model\CarPlace;
use app\model\House;
use app\model\Member;
use app\model\MemberCar;
use app\model\MemberHouse;
use app\model\OrderItem;
use app\model\Room;
use think\db\exception\DataNotFoundException;
use think\db\exception\DbException;
use think\db\exception\ModelNotFoundException;
use think\facade\Request;
use think\facade\Db;
class Excel
{
    //excel大数据导出demo https://www.cnblogs.com/dcb3688/p/4607979.html

    public function out(){

        require_once root_path().'extend/phpoffice/phpexcel/Classes/PHPExcel.php';
        define('EOL', '<br />');
        $objPHPExcel = new \PHPExcel();
        ini_set("memory_limit", "1024M"); // 设置php可使用内存
        set_time_limit(0);
        error_reporting(E_ALL);
        date_default_timezone_set('Asia/Shanghai');
        $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
        if (!\PHPExcel_Settings::setCacheStorageMethod($cacheMethod)) {
            die($cacheMethod . " 缓存方法不可用" . EOL);
        }
        echo date('H:i:s'), " 当前使用的缓存方法是： ", $cacheMethod, " 方式", EOL;
        echo date('H:i:s'), " 开始设置文档属性", EOL;
        $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
            ->setLastModifiedBy("Maarten Balliauw")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Test result file");


        echo date('H:i:s'), " 开始添加单元格标题", EOL;
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "订单号");
        $objPHPExcel->getActiveSheet()->setCellValue('B1', "姓名");
        $objPHPExcel->getActiveSheet()->setCellValue('C1', "手机号");
        $objPHPExcel->getActiveSheet()->setCellValue('D1', "小区");
        $objPHPExcel->getActiveSheet()->setCellValue('E1', "房号");
        $objPHPExcel->getActiveSheet()->setCellValue('F1', "费用类型");
        $objPHPExcel->getActiveSheet()->setCellValue('G1', "价钱");
        $objPHPExcel->getActiveSheet()->setCellValue('H1', "年限");
        $objPHPExcel->getActiveSheet()->setCellValue('I1', "缴费状态");
        $objPHPExcel->getActiveSheet()->setCellValue('J1', "创建时间");
        // 设置单元格宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(50);

        /**
         * 左对齐与 右对齐
         * 可设置整列->getStyle('N')   可针对行rows设置getStyle('N3')
         */
//        $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
//        $objPHPExcel->getActiveSheet()->getStyle('B3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
//
//        echo date('H:i:s'), " 设置隐藏C D列", EOL;
//        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setVisible(false);
//        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setVisible(false);
//
//        echo date('H:i:s'), " 设置大纲级别", EOL;
//        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setOutlineLevel(1)
//            ->setVisible(false)
//            ->setCollapsed(true);

        /**
         * 开始添加数据
         */
//        for ($i = 2; $i <= 100; $i++) {
//            $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, "FName $i")
//                ->setCellValue('B' . $i, "LName $i")
//                ->setCellValue('C' . $i, "PhoneNo $i")
//                ->setCellValue('D' . $i, "FaxNo $i")
//                ->setCellValue('E' . $i, true);
//        }
        $param = Request::param();
        $where = [];
//        $where[] = ['r.house_id','=',$param['id']] ;
        if(!empty($param['house_id'])){
            $where[] = ['house_id','=',$param['house_id']];
        }
        if(!empty($param['status'])){
            $where[] = ['status','=',$param['status']];
        }
        if(!empty($param['title'])){
            $where[] = ['title','like','%'.$param['title'].'%'];
        }
        if(empty($param['year'])){
            fail('请选择年限');
        }
        if(empty($param['house_id'])){
            fail('请选择小区');
        }
        if(!empty($param['year'])){
            $where[] = ['year','=',$param['year']];
        }

//        $where[] = ['mhp.year','=',$param['year']] ;
//        $data = Room::alias('r')
//            ->join('MemberHouse mh','r.id = mh.room_id','left')
////            ->join('MemberCar mc','mc.member_id = mh.member_id','left')
//            ->join('MemberHousePay mhp','r.id = mhp.room_id and mhp.year = '.$param['year'],'left')
////            ->field('r.*,mhp.*')
//            ->field('r.id,r.house,r.gelou,r.loucheng,r.title,r.house_id,mh.name,mh.mobile,
//            mhp.total_price,
//            mhp.house_price,
//            mhp.lift_price,
//            mhp.car_price,
//            mhp.status_text')
//            ->where($where)
//            ->group('r.title')
//            ->order('r.id asc')
//            ->select();



        $data = OrderItem::where($where)->select();
        $az = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
        foreach ($data as $k => $v){

            $i = $k + 2;

//            $objPHPExcel->getActiveSheet()->setCellValue('A'.$i,$i-1);
            $objPHPExcel->getActiveSheet()->setCellValue('A'.$i,$v['order_number']);
            $objPHPExcel->getActiveSheet()->setCellValue("B".$i,$v->Member->name);
            $objPHPExcel->getActiveSheet()->setCellValue("C".$i,"\t".$v->Member->mobile);
            $objPHPExcel->getActiveSheet()->setCellValue("D".$i,$v['house']);
            $objPHPExcel->getActiveSheet()->setCellValue("E".$i,$v['room']);
            $objPHPExcel->getActiveSheet()->setCellValue("F".$i,$v->order_type_title);
            $objPHPExcel->getActiveSheet()->setCellValue("G".$i,$v->price2);
            $objPHPExcel->getActiveSheet()->setCellValue("H".$i,$v->year);
            $objPHPExcel->getActiveSheet()->setCellValue("I".$i,$v->status_text);
            $objPHPExcel->getActiveSheet()->setCellValue("J".$i,$v->create_time);
//            $objPHPExcel->getActiveSheet()->setCellValue("K".$i,$v->car_price);
//            $objPHPExcel->getActiveSheet()->setCellValue("L".$i,$v->car_price);




//            // 先获取字符串的长度
//            $len = strlen($v['house'])+2;
//            // 获取第A行，设置宽度
//            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth($len);
//            $len = strlen("\t".$v->mobile)+2;
//            // 获取第A行，设置宽度
//            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth($len);

        }

        //设置单元列的宽度
        foreach ($az as $az_v){
            $objPHPExcel->getActiveSheet()->getColumnDimension($az_v)->setWidth(20);
        }

        $house = House::where('id',$param['house_id'])->value('title');
        $filename =  $house.$param['year'].'年物业缴费表';
        $objPHPExcel->getActiveSheet()->setTitle($house);
        echo date('H:i:s'), " 设置格式为Excel2007版格式", EOL;
        $callStartTime = microtime(true);

        //$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        //$objWriter->save(str_replace('.php', '.xlsx', __FILE__));

        ob_end_clean();
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;charset=utf-8;filename="' . $filename . '.xlsx"');
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');

        $callEndTime = microtime(true);
        $callTime = $callEndTime - $callStartTime;

        echo date('H:i:s'), " 设置生成的文件为： ", str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)), EOL;
        echo date('H:i:s'), ' 写入Workbook中耗时 ', sprintf('%.4f', $callTime), " 秒", EOL;
        echo date('H:i:s'), ' 当前内存使用情况: ', (memory_get_usage(true) / 1024 / 1024), " MB", EOL;
        echo date('H:i:s'), " 内存使用峰值: ", (memory_get_peak_usage(true) / 1024 / 1024), " MB", EOL;
        echo date('H:i:s'), " 完成写入文件", EOL;
        echo date('H:i:s'), ' 文件被创建在： ', getcwd(), '目录', EOL;
    }

    //https://www.cnblogs.com/chrdai/p/8581026.html
    public function in($filename = ''){

        require_once root_path().'extend/phpoffice/phpexcel/Classes/PHPExcel.php';
        $filename = uploadOne('read','file',true);


        $filename = iconv('utf-8', 'gb2312', $filename);

        if (empty($filename) || !file_exists($filename)){
            die('file not exists');
        }
        // 兼容 Excel03 和 Excel07
        $objRead = new \PHPExcel_Reader_Excel2007();
        if (!$objRead->canRead($filename)){
            $objRead = new \PHPExcel_Reader_Excel5();
            if (!$objRead->canRead($filename)){
                die('No Excel');
            }
        }

        // 获取 excel 中的文件内容
        $objPHPExcel = \PHPExcel_IOFactory::load($filename);

        // 一次性取出整个 sheet 内的内容（不常用）
//    $sheetCount = $objPHPExcel->getSheetCount();
//    for ($i=0; $i<$sheetCount; $i++){
//        $data = $objPHPExcel->getSheet($i)->toArray();
//        var_dump($data);
//    }

        // 逐行读取 sheet 内的内容（常用）
        foreach ($objPHPExcel->getWorksheetIterator() as $sheet){   //循环sheet
            foreach ($sheet->getRowIterator() as $row){     //循环row
                if ($row->getRowIndex() <= 3){  // 1的时候默认从第二行开始

                    continue 2;
                }
                $data = [];
                foreach ($row->getCellIterator() as $cell){     //循环cell
                    $v = $cell->getValue();
                    $data[] = $v;
                }


                $data = unsetNull($data);

                Db::startTrans();
                try {
                    $find_house = (new House())->where('title', $data[1])->find();
                    if (empty($find_house)) {
                        $ret = House::create(['title' => $data[1]]);
                        $house_id = $ret->id;
                        $house_title = $ret->title;
                    } else {
                        $house_id = $find_house['id'];
                        $house_title = $find_house['title'];
                    }
                    $find_room = Room::where('title', $data[2])->where('house_id',$house_id)->find();
                    $total_price = $data[7] + $data[8] + (int)$data[10];
                    if (empty($find_room)) {

                        $ret = Room::create(['name'=>$data[5],'mobile'=>$data[6],'house' => $data[1], 'gelou'=>$data[3],'loucheng'=>$data[4],'house_id' => $house_id, 'title' => $data[2], 'house_price' => $data[7], 'lift_price' => $data[8], 'car_price' => $data[10], 'total_price' => $total_price]);
                        $room_id = $ret->id;
                        $room_title = $ret->title ;
                    } else {
                        $find_room->save(['name'=>$data[5],'mobile'=>$data[6],'house' => $data[1], 'gelou'=>$data[3],'loucheng'=>$data[4],'house_id' => $house_id, 'title' => $data[2], 'house_price' => $data[7], 'lift_price' => $data[8], 'car_price' => $data[10], 'total_price' => $total_price]);
                        $room_id = $find_room['id'];
                        $room_title = $find_room['title'];
                    }
                    $find_mobile = Member::where('mobile', $data[6])->find();
                    if (empty($find_mobile)) {
                        $ret = Member::create(['mobile' => $data[6], 'name' => $data[5]]);
                        $member_id = $ret->id;
                    } else {
                        $member_id = $find_mobile['id'];
                    }
                    $find_member_house = MemberHouse::where([
//                        ['member_id', '=', $member_id],
                        ['house_id', '=', $house_id],
                        ['room_id', '=', $room_id]
                    ])->find();
                    if (empty($find_member_house)) {
                        MemberHouse::create([
                            'member_id' => $member_id,
                            'house_id' => $house_id,
                            'room_id' => $room_id,
                            'house'=> $house_title,
                            'room'=> $room_title,
                            'mobile' => $data[6],
                            'type'=> '个人',
                            'role' => '业主',
                            'status'=> 2,
                            'name' => $data[5]
                        ]);
                    } //$data[9]车位 $data[11]车牌

                    $car_place = !empty($data[9]) ? explode('、', $data[9]) : [];

                    $car_number = !empty($data[11]) ? explode('、', $data[11]) : [];//添加小区车位;
                    if (!empty($car_place)) { //否者为空的一个数组$car_number[0];
                        foreach ($car_place as $k_n => $car_v) {
                            $find_car_place = CarPlace::where([
                                ['house_id', '=', $house_id],
                                ['title', '=', $car_v]
                            ])->find();
                            if (empty($find_car_place)) {
                                $ret = CarPlace::create(['house_id' => $house_id, 'house' => $data[1], 'title' => $car_v]);
                                $car_place_id = $ret->id;
                                $car_place_title = $ret->title;

                            }else{
                                $car_place_id = $find_car_place['id'];
                                $car_place_title = $find_car_place['title'];;
                            }
                                                    $find_car = MemberCar::where([
                                                        ['member_id','=',$member_id],
                                                        ['title','=',$car_v],
                                                    ])->find();
                                                    if(empty($find_car)){
                                                        $title =  !empty($car_number[$k_n]) ? $car_number[$k_n] : '';
                                                        MemberCar::create([
                                                            'member_id'=>$member_id,
                                                            'house_id'=> $house_id,
                                                            'house_title'=> $data[1],
                                                            'car_place_id' => $car_place_id,
                                                            'car_place_title' => $car_place_title,
                                                            'name' => $data[5],
                                                            'mobile' => $data[6],
                                                            'title'=>$title]);
                                                    }
                        }
                    }

                    if (!empty($car_number)) {
                        foreach ($car_number as $k => $car_n_v) {
//                            dump($car_n_v);
//                            dump($car_place);
//
                                if(!empty($car_place[$k])){
                                  //dump($car_place[$k]);
                                    $now_car_place = $car_place[$k]; //当前对应车牌的车位号

                                    //$car_place = CarPlace::where([['title', '=', $now_car_place]])->find();//fetchsql(true)->

                                    //dump($car_place);
                                   /// MemberCar::create(['member_id' => $member_id, 'house_id' => $house_id, 'car_place_id' => $car_place['id'], 'car_place_title' => $car_place['title'], 'name' => $data[5], 'mobile' => $data[6]]);
                                }
//
                        }

                    }


                    Db::commit();
                } catch (DbException $e) {
                    Db::rollback();
                    halt(['line'=>$e->getLine(),'message'=>$e->getMessage()]);
                }



            }
        }
        win('导入成功');

    }
}